using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Collections;
using System.Data.Common;
using System.Data;

namespace OA.Common
{
    /// <summary>
    /// 数据库访问对象
    /// </summary>
    public  class DbHelper
    {
        #region 私有成员

        private DbProviderFactory factory;

        /// <summary>
        /// 数据库事务对象
        /// </summary>
        private DbTransaction trans = null;

        private DbConnection Connection=null;

        #endregion 

        /// <summary>
        /// 初始化
        /// </summary>
        /// <param name="factory">数据库对象创建工厂</param>
        /// <param name="connnectionstring">连接字符串</param>
        public DbHelper(DbProviderFactory factory,string connnectionstring)
        {
            this.factory = factory;
            Connection = factory.CreateConnection();
            Connection.ConnectionString = connnectionstring;
        }

        #region 属性
        /// <summary>
        /// 是否开始了事务
        /// </summary>
        public bool IsBeginTransaction
        {
            get
            {
                return trans != null;
            }
        }


        /// <summary>
        /// 获取连接字符串
        /// </summary>
        public string ConnectionString
        {
            get
            {
                return Connection.ConnectionString;
            }
        }

        /// <summary>
        /// 获取连接类型
        /// </summary>
        public Type ConnectionType
        {
            get
            {
                return Connection.GetType();
            }
        }

        #endregion 

        #region 数据库事务
        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTransaction()
        {
            if (IsBeginTransaction) throw new Exception("事务已经开始");
            if(Connection.State!=ConnectionState.Open) Connection.Open();
            trans = Connection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            CommitTransaction(true);
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction(bool closeConnection)
        {
            if (!IsBeginTransaction) throw new Exception("尚未开始事务");
            trans.Commit();
            trans = null;
            if(closeConnection) Connection.Close();

        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            RollbackTransaction(false);
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction(bool closeConnection)
        {
            if (!IsBeginTransaction) throw new Exception("尚未开始事务");
            trans.Rollback();
            trans = null;
            if (closeConnection) Connection.Close();
        }
        #endregion

        #region ExecuteNonQuery 执行SQL

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteNonQuery(CommandType.Text, cmdText, commandParameters);
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="cmdType">SQL字符串类型</param>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            int ret;
            DbCommand cmd = PrepareCommand(Connection, cmdType, cmdText, commandParameters);
            ret = cmd.ExecuteNonQuery();
            if (!IsBeginTransaction) Connection.Close();
            return ret;
        }

        #endregion

        #region ExecuteReader 查询

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>查询到的记录集</returns>
        public IDataReader ExecuteReader(string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteReader(CommandType.Text, cmdText, commandParameters);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="cmdType">SQL字符串类型</param>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>查询到的记录集</returns>
        public IDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = PrepareCommand(Connection, cmdType, cmdText, commandParameters);
            IDataReader dr=null;
            if (!IsBeginTransaction)
            {
                dr=cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            else
            {
                dr=cmd.ExecuteReader();
            }
            cmd.Parameters.Clear();
            return dr;
        }

        #endregion 

        #region ExecuteScalar 查询

                /// <summary>
        /// 查询
        /// </summary>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>结果的第一行第一列</returns>
        public object ExecuteScalar(string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteScalar(CommandType.Text, cmdText, commandParameters);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="cmdType">SQL字符串类型</param>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>结果的第一行第一列</returns>
        public object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = PrepareCommand(Connection, cmdType, cmdText, commandParameters);
            object val = cmd.ExecuteScalar();
            cmd.Parameters.Clear();
            if(trans==null) Connection.Close();
            return val;
        }
        #endregion

        #region ExecuteDataSet 查询

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>查询到的记录集</returns>
        public DataSet ExecuteDataSet(string cmdText, params DbParameter[] commandParameters)
        {
            return ExecuteDataSet(CommandType.Text, cmdText, commandParameters);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="cmdType">SQL字符串类型</param>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>查询到的记录集</returns>
        public DataSet ExecuteDataSet(CommandType cmdType, string cmdText, params DbParameter[] commandParameters)
        {
            DbCommand cmd = PrepareCommand(Connection, cmdType, cmdText, commandParameters);
            var ida = factory.CreateDataAdapter();
            ida.SelectCommand = cmd;
            DataSet ds = new DataSet();
            ida.Fill(ds);
            if (!IsBeginTransaction) Connection.Close();
            return ds;
        }
        #endregion

        #region PrepareCommand 构造命令对象
        /// <summary>
        /// 构造命令对象
        /// </summary>
        /// <param name="connection">数据库连接对象</param>
        /// <param name="trans">数据库事务对象</param>
        /// <param name="cmdType">SQL字符串类型</param>
        /// <param name="cmdText">SQL</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>命令对象</returns>
        private DbCommand PrepareCommand(DbConnection connection, CommandType cmdType, string cmdText, DbParameter[] cmdParms)
        {
            DbCommand cmd = connection.CreateCommand();
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (IsBeginTransaction)
            {
                cmd.Transaction = trans;
            }
            else
            {
                if (Connection.State != ConnectionState.Open) Connection.Open();
            }
            if (cmdParms != null)
            {
                foreach (DbParameter parm in cmdParms)
                {
                    if (parm.Value == null) parm.Value = DBNull.Value;
                    cmd.Parameters.Add(parm);
                }
            }

            return cmd;
        }
        #endregion 
    }
}
